package me.zhengjie.dailyPoem.mapper;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import me.zhengjie.poemPoet.domain.PoemPoet;
import me.zhengjie.poemmanager.domain.Query;
import me.zhengjie.poemmanager.domain.UserInfoCount;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Mapper
@Transactional
public interface PoemRecommendMapper {
    @Select("SELECT author_name,dynasty,SUBSTRING_INDEX(content, '。', 1) As content FROM `poem_poet` ORDER BY RAND() LIMIT 1")
    PoemPoet poemRecommend();

    /* 根据日期推荐每日一诗*/
    @Select("SELECT * FROM poem_poet where poem_id = #{poemId}")
        PoemPoet getDailyPoem(int poemId);

    @Select("SELECT post.post_id ,\n" +
            "       SUBSTRING(post.content, 1, 20) AS excerpt,\n" +
            "       post.title,\n" +
            "       post.user_name,\n" +
            "       post.like_count AS like_count,\n" +
            "       post.comment_count AS comment_count\n" +
            "FROM post\n" +
            "INNER JOIN tag t ON t.object_id = post.post_id\n" +
            "INNER JOIN poem_poet  ON poem_poet.title LIKE CONCAT('%', t.tag_name, '%') \n" +
            "WHERE poem_poet.title LIKE CONCAT('%', t.tag_name, '%')\n" +
            "GROUP BY post.post_id;")
   List<PoemPoet> getRelatedPost();

    @Select("SELECT \n" +
            "    COUNT(DISTINCT poem_user.poem_id) AS poem_count,\n" +
            "    COUNT(DISTINCT post.post_id) AS post_count,\n" +
            "    COUNT(DISTINCT comment.comment_id) AS comment_count \n" +
            "FROM \n" +
            "    poem_user \n" +
            "JOIN \n" +
            "    post ON poem_user.author_name = post.user_name \n" +
            "JOIN \n" +
            "    comment ON poem_user.author_name = comment.user_name \n" +
            "WHERE \n" +
            "  poem_user.author_name =#{userName};")
    UserInfoCount getRealtedInfo(String userName);

    @Select("SELECT NULL AS source, 0 AS type, NULL AS comments, post.post_id AS postId, NULL AS likeCount, NULL AS commentCount, post.user_name AS userName, post.poem_author AS poemAuthor, post.status, post.title, NULL AS subTitle, NULL AS dynasty, NULL AS author_id, NULL AS authorId, post.created_at, post.updated_at, SUBSTRING(post.content, 1, 50) AS excerpt, post.content, NULL AS poemId, post.user_name AS authorName\n" +
            "FROM post\n" +
            "LEFT JOIN tag ON tag.tag_name = #{keyWord} AND tag.object_type = 0\n" +
            "WHERE (#{keyWord} <> '' AND (\n" +
            "    post.title LIKE CONCAT('%', #{keyWord}, '%')\n" +
            " OR post.user_name LIKE CONCAT('%', #{keyWord}, '%')\n" +
            " OR post.post_id = tag.object_id\n" +
            " OR to_pinyin(post.title) LIKE CONCAT('%', #{keyWord}, '%')\n" +
            " OR to_pinyin(post.user_name) LIKE CONCAT('%', #{keyWord}, '%')\n" +
            "))\n" +
            "UNION ALL\n" +
            "SELECT NULL AS source, 1 AS type, NULL AS comments, NULL AS postId, NULL AS likeCount, NULL AS commentCount, NULL AS userName, NULL AS poemAuthor, NULL AS status, pu.title, pu.sub_title AS subTitle, NULL AS dynasty, NULL AS author_id, NULL AS authorId, pu.created_at, pu.updated_at, SUBSTRING(pu.content, 1, 50) AS excerpt, pu.content, pu.poem_id AS poemId, pu.author_name AS authorName\n" +
            "FROM poem_user pu\n" +
            "LEFT JOIN tag ON tag.tag_name = #{keyWord} AND tag.object_type = 1\n" +
            "WHERE (#{keyWord} <> '' AND (\n" +
            "    pu.title LIKE CONCAT('%', #{keyWord}, '%')\n" +
            " OR pu.author_name LIKE CONCAT('%', #{keyWord}, '%')\n" +
            " OR pu.poem_id = tag.object_id\n" +
            " OR to_pinyin(pu.title) LIKE CONCAT('%', #{keyWord}, '%')\n" +
            " OR to_pinyin(pu.author_name) LIKE CONCAT('%', #{keyWord}, '%')\n" +
            "))\n" +
            "UNION ALL\n" +
            "SELECT NULL AS source, 2 AS type, NULL AS comments, NULL AS postId, NULL AS likeCount, NULL AS commentCount, NULL AS userName, NULL AS poemAuthor, NULL AS status, pp.title, NULL AS subTitle, pp.dynasty, pp.author_id AS author_id, pp.author_id AS authorId, pp.created_at, pp.updated_at, SUBSTRING(pp.content, 1, 50) AS excerpt, pp.content, pp.poem_id AS poemId, pp.author_name AS authorName\n" +
            "FROM poem_poet pp\n" +
            "WHERE (#{keyWord} <> '' AND (\n" +
            "    pp.title LIKE CONCAT('%', #{keyWord}, '%')\n" +
            " OR pp.author_name LIKE CONCAT('%', #{keyWord}, '%')\n" +
            " OR to_pinyin(pp.title) LIKE CONCAT('%', #{keyWord}, '%')\n" +
            " OR to_pinyin(pp.author_name) LIKE CONCAT('%', #{keyWord}, '%')\n" +
            "))\n" +
            "UNION ALL\n" +
            "SELECT NULL AS source, 3 AS type, NULL AS comments, NULL AS postId, rec.like_count AS likeCount, rec.comment_count AS commentCount, rec.author_name AS userName, NULL AS poemAuthor, NULL AS status, rec.title, NULL AS subTitle, NULL AS dynasty, rec.author_id AS author_id, rec.author_id AS authorId, rec.created_at, rec.updated_at, SUBSTRING(rec.content, 1, 50) AS excerpt, rec.content, rec.poem_id AS poemId, rec.author_name AS authorName\n" +
            "FROM recitation rec\n" +
            "WHERE (#{keyWord} <> '' AND (\n" +
            "    rec.title LIKE CONCAT('%', #{keyWord}, '%')\n" +
            " OR rec.author_name LIKE CONCAT('%', #{keyWord}, '%')\n" +
            " OR rec.poem_title LIKE CONCAT('%', #{keyWord}, '%')\n" +
            " OR to_pinyin(rec.title) LIKE CONCAT('%', #{keyWord}, '%')\n" +
            " OR to_pinyin(rec.author_name) LIKE CONCAT('%', #{keyWord}, '%')\n" +
            " OR to_pinyin(rec.poem_title) LIKE CONCAT('%', #{keyWord}, '%')\n" +
            "))")
    List<Query> queryInfo(String keyWord);


    @Select("SELECT NULL AS source, 0 AS type, NULL AS comments, post.post_id AS postId, NULL AS likeCount, NULL AS commentCount, post.user_name AS userName, post.poem_author AS poemAuthor, post.status, post.title, NULL AS subTitle, NULL AS dynasty, NULL AS author_id, NULL AS authorId, post.created_at, post.updated_at, SUBSTRING(post.content, 1, 50) AS excerpt, post.content, NULL AS poemId, post.user_name AS authorName\n" +
            "FROM post\n" +
            "LEFT JOIN tag ON tag.tag_name = #{keyWord} AND tag.object_type = 0\n" +
            "WHERE (#{keyWord} <> '' AND (\n" +
            "    post.title LIKE CONCAT('%', #{keyWord}, '%')\n" +
            " OR post.user_name LIKE CONCAT('%', #{keyWord}, '%')\n" +
            " OR post.post_id = tag.object_id\n" +
            " OR to_pinyin(post.title) LIKE CONCAT('%', #{keyWord}, '%')\n" +
            " OR to_pinyin(post.user_name) LIKE CONCAT('%', #{keyWord}, '%')\n" +
            "))\n" +
            "UNION ALL\n" +
            "SELECT NULL AS source, 1 AS type, NULL AS comments, NULL AS postId, NULL AS likeCount, NULL AS commentCount, NULL AS userName, NULL AS poemAuthor, NULL AS status, pu.title, pu.sub_title AS subTitle, NULL AS dynasty, NULL AS author_id, NULL AS authorId, pu.created_at, pu.updated_at, SUBSTRING(pu.content, 1, 50) AS excerpt, pu.content, pu.poem_id AS poemId, pu.author_name AS authorName\n" +
            "FROM poem_user pu\n" +
            "LEFT JOIN tag ON tag.tag_name = #{keyWord} AND tag.object_type = 1\n" +
            "WHERE (#{keyWord} <> '' AND (\n" +
            "    pu.title LIKE CONCAT('%', #{keyWord}, '%')\n" +
            " OR pu.author_name LIKE CONCAT('%', #{keyWord}, '%')\n" +
            " OR pu.poem_id = tag.object_id\n" +
            " OR to_pinyin(pu.title) LIKE CONCAT('%', #{keyWord}, '%')\n" +
            " OR to_pinyin(pu.author_name) LIKE CONCAT('%', #{keyWord}, '%')\n" +
            "))\n" +
            "UNION ALL\n" +
            "SELECT NULL AS source, 2 AS type, NULL AS comments, NULL AS postId, NULL AS likeCount, NULL AS commentCount, NULL AS userName, NULL AS poemAuthor, NULL AS status, pp.title, NULL AS subTitle, pp.dynasty, pp.author_id AS author_id, pp.author_id AS authorId, pp.created_at, pp.updated_at, SUBSTRING(pp.content, 1, 50) AS excerpt, pp.content, pp.poem_id AS poemId, pp.author_name AS authorName\n" +
            "FROM poem_poet pp\n" +
            "WHERE (#{keyWord} <> '' AND (\n" +
            "    pp.title LIKE CONCAT('%', #{keyWord}, '%')\n" +
            " OR pp.author_name LIKE CONCAT('%', #{keyWord}, '%')\n" +
            " OR to_pinyin(pp.title) LIKE CONCAT('%', #{keyWord}, '%')\n" +
            " OR to_pinyin(pp.author_name) LIKE CONCAT('%', #{keyWord}, '%')\n" +
            "))\n" +
            "UNION ALL\n" +
            "SELECT NULL AS source, 3 AS type, NULL AS comments, NULL AS postId, rec.like_count AS likeCount, rec.comment_count AS commentCount, rec.author_name AS userName, NULL AS poemAuthor, NULL AS status, rec.title, NULL AS subTitle, NULL AS dynasty, rec.author_id AS author_id, rec.author_id AS authorId, rec.created_at, rec.updated_at, SUBSTRING(rec.content, 1, 50) AS excerpt, rec.content, rec.poem_id AS poemId, rec.author_name AS authorName\n" +
            "FROM recitation rec\n" +
            "WHERE (#{keyWord} <> '' AND (\n" +
            "    rec.title LIKE CONCAT('%', #{keyWord}, '%')\n" +
            " OR rec.author_name LIKE CONCAT('%', #{keyWord}, '%')\n" +
            " OR rec.poem_title LIKE CONCAT('%', #{keyWord}, '%')\n" +
            " OR to_pinyin(rec.title) LIKE CONCAT('%', #{keyWord}, '%')\n" +
            " OR to_pinyin(rec.author_name) LIKE CONCAT('%', #{keyWord}, '%')\n" +
            " OR to_pinyin(rec.poem_title) LIKE CONCAT('%', #{keyWord}, '%')\n" +
            "))")
    IPage<Query> queryInfoPaged(
            Page<?> page,
            @Param("keyWord") String keyWord
    );
}
